//*** Guidance generated code ***//
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Diagnostics.Contracts;
using System.Globalization;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Web.Configuration;
using System.Web.Hosting;
using System.Web.Security;

using ProjectBase.Core;

namespace $safeprojectname$.Security
{
    public sealed class SqlMembershipProvider : MembershipProvider
    {
        string _connectionString;
        MachineKeySection _machineKey; //Used when determining encryption key values.

        const string EventLog = "Application";
        const string EventSource = "SqlMembershipProvider";
        const string ExceptionMessage = "An exception occurred. Please check the Event Log.";
        const int NewPasswordLength = 8;

        #region properties
        bool _enablePasswordReset;
        bool _enablePasswordRetrieval;
        int _maxInvalidPasswordAttempts;
        int _minRequiredNonAlphanumericCharacters;
        int _minRequiredPasswordLength;
        int _passwordAttemptWindow;
        MembershipPasswordFormat _passwordFormat;
        string _passwordStrengthRegularExpression;
        bool _requiresQuestionAndAnswer;
        bool _requiresUniqueEmail;
        bool _writeExceptionsToEventLog;

        public bool WriteExceptionsToEventLog
        {
            get { return _writeExceptionsToEventLog; }
            set { _writeExceptionsToEventLog = value; }
        }

        public override string ApplicationName { get; set; }

        public override bool EnablePasswordReset
        {
            get { return _enablePasswordReset; }
        }

        public override bool EnablePasswordRetrieval
        {
            get { return _enablePasswordRetrieval; }
        }

        public override bool RequiresQuestionAndAnswer
        {
            get { return _requiresQuestionAndAnswer; }
        }

        public override bool RequiresUniqueEmail
        {
            get { return _requiresUniqueEmail; }
        }

        public override int MaxInvalidPasswordAttempts
        {
            get { return _maxInvalidPasswordAttempts; }
        }

        public override int PasswordAttemptWindow
        {
            get { return _passwordAttemptWindow; }
        }

        public override MembershipPasswordFormat PasswordFormat
        {
            get { return _passwordFormat; }
        }

        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return _minRequiredNonAlphanumericCharacters; }
        }

        public override int MinRequiredPasswordLength
        {
            get { return _minRequiredPasswordLength; }
        }

        public override string PasswordStrengthRegularExpression
        {
            get { return _passwordStrengthRegularExpression; }
        }
        #endregion properties

        #region Init
        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (name.IsEmpty())
            {
                name = "SqlMembershipProvider";
            }

            if (config["description"].IsEmpty())
            {
                config.Remove("description");
                config.Add("description", "Sql Membership provider");
            }

            // Initialize the abstract base class.
            base.Initialize(name, config);

            ApplicationName = GetConfigValue(config["applicationName"], HostingEnvironment.ApplicationVirtualPath);
            _maxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"), CultureInfo.InvariantCulture);
            _passwordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"), CultureInfo.InvariantCulture);
            _minRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"), CultureInfo.InvariantCulture);
            _minRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"), CultureInfo.InvariantCulture);
            _passwordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""), CultureInfo.InvariantCulture);
            _enablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"), CultureInfo.InvariantCulture);
            _enablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"), CultureInfo.InvariantCulture);
            _requiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"), CultureInfo.InvariantCulture);
            _requiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"), CultureInfo.InvariantCulture);
            _writeExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config["writeExceptionsToEventLog"], "true"), CultureInfo.InvariantCulture);


            var temp_format = config["passwordFormat"];
            if (temp_format == null)
            {
                temp_format = "Hashed";
            }

            switch (temp_format)
            {
                case "Hashed":
                    _passwordFormat = MembershipPasswordFormat.Hashed;
                    break;
                case "Encrypted":
                    _passwordFormat = MembershipPasswordFormat.Encrypted;
                    break;
                case "Clear":
                    _passwordFormat = MembershipPasswordFormat.Clear;
                    break;
                default:
                    throw new ProviderException("Password format not supported.");
            }

            var ConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if (ConnectionStringSettings == null
                || string.IsNullOrWhiteSpace(ConnectionStringSettings.ConnectionString))
            {
                throw new ProviderException("Connection string cannot be blank.");
            }

            _connectionString = ConnectionStringSettings.ConnectionString;

            //SqlMembershipUtils.CreateDatabaseIfRequired(_connectionString, ApplicationName);

            //_applicationId = SqlMembershipUtils.GetApplicationId(_connectionString, ApplicationName);

            Configuration cfg =
              WebConfigurationManager.OpenWebConfiguration(HostingEnvironment.ApplicationVirtualPath);
            _machineKey = (MachineKeySection)cfg.GetSection("system.web/machineKey");
        }

        string GetConfigValue(string configValue, string defaultValue)
        {
            return configValue.IsEmpty() ? defaultValue : configValue;
        }
        #endregion Init

        #region  System.Web.Security.MembershipProvider
        public override bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            if (!ValidateUser(username, oldPassword))
            {
                return false;
            }


            var args = new ValidatePasswordEventArgs(username, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                {
                    throw args.FailureInformation;
                }
                throw new MembershipPasswordException("Change password canceled due to new password validation failure.");
            }

            var rowsAffected = 0;

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("UPDATE [ServicesMembership] " +
                                                  " SET Password = @Password, LastPasswordChangedDate = @LastPasswordChangedDate " +
                                                  " WHERE UserName = @UserName ", conn))
                {
                    cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 128).Value = EncodePassword(newPassword, username);
                    cmd.Parameters.Add("@LastPasswordChangedDate", SqlDbType.DateTime).Value = DateTime.UtcNow;
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                    try
                    {
                        conn.Open();

                        rowsAffected = cmd.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "ChangePassword");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
            return rowsAffected > 0;
        }

        public override bool ChangePasswordQuestionAndAnswer(string username,
            string password,
            string newPasswordQuestion,
            string newPasswordAnswer)
        {
            var rowsAffected = 0;

            if (!ValidateUser(username, password))
            {
                return false;
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("UPDATE [ServicesMembership] " +
                                                  " SET PasswordQuestion = @Question, PasswordAnswer = @Answer" +
                                                  " WHERE UserName = @UserName ", conn))
                {
                    cmd.Parameters.Add("@Question", SqlDbType.NVarChar, 256).Value = newPasswordQuestion;
                    cmd.Parameters.Add("@Answer", SqlDbType.NVarChar, 128).Value = EncodePassword(newPasswordAnswer, username);
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                    try
                    {
                        conn.Open();

                        rowsAffected = cmd.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "ChangePasswordQuestionAndAnswer");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
            return rowsAffected > 0;
        }


        public override MembershipUser CreateUser(string username,
            string password,
            string email,
            string passwordQuestion,
            string passwordAnswer,
            bool isApproved,
            object providerUserKey,
            out MembershipCreateStatus status)
        {
            var args = new ValidatePasswordEventArgs(username, password, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            if (RequiresUniqueEmail && !string.IsNullOrWhiteSpace(GetUserNameByEmail(email)))
            {
                status = MembershipCreateStatus.DuplicateEmail;
                return null;
            }

            var u = GetUser(username, false);

            if (u == null)
            {
                var createDate = DateTime.UtcNow;

                if (providerUserKey == null)
                {
                    providerUserKey = Guid.NewGuid();
                }
                else
                {
                    if (!(providerUserKey is Guid))
                    {
                        status = MembershipCreateStatus.InvalidProviderUserKey;
                        return null;
                    }
                }

                passwordQuestion = string.IsNullOrWhiteSpace(passwordQuestion) ? string.Empty : passwordQuestion;
                passwordAnswer = string.IsNullOrWhiteSpace(passwordAnswer) ? string.Empty : passwordAnswer;

                using (var conn = new SqlConnection(_connectionString))
                {
                    using (var cmd = new SqlCommand(
                        @"INSERT INTO [ServicesMembership]
                           ([UserName]
                           ,[Password]
                           ,[PasswordFormat]
                           ,[Email]
                           ,[LoweredEmail]
                           ,[PasswordQuestion]
                           ,[PasswordAnswer]
                           ,[IsApproved]
                           ,[IsLockedOut]
                           ,[CreateDate]
                           ,[LastLoginDate]
                           ,[LastPasswordChangedDate]
                           ,[LastLockoutDate]
                           ,[FailedPasswordAttemptCount]
                           ,[FailedPasswordAttemptWindowStart]
                           ,[FailedPasswordAnswerAttemptCount]
                           ,[FailedPasswordAnswerAttemptWindowStart]
                           ,[Comment])
                     VALUES
                           (@UserName
                           ,@Password
                           ,@PasswordFormat
                           ,@Email
                           ,@LoweredEmail
                           ,@PasswordQuestion
                           ,@PasswordAnswer
                           ,@IsApproved
                           ,@IsLockedOut
                           ,@CreateDate
                           ,@LastLoginDate
                           ,@LastPasswordChangedDate
                           ,@LastLockoutDate
                           ,@FailedPasswordAttemptCount
                           ,@FailedPasswordAttemptWindowStart
                           ,@FailedPasswordAnswerAttemptCount
                           ,@FailedPasswordAnswerAttemptWindowStart
                           ,@Comment)", conn))
                    {
                        cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;
                        cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 128).Value = EncodePassword(password, username);
                        cmd.Parameters.Add("@PasswordFormat", SqlDbType.Int).Value = PasswordFormat.GetHashCode();
                        cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = email;
                        cmd.Parameters.Add("@LoweredEmail", SqlDbType.NVarChar, 256).Value = email.ToLowerInvariant();
                        cmd.Parameters.Add("@PasswordQuestion", SqlDbType.NVarChar, 256).Value = passwordQuestion;
                        cmd.Parameters.Add("@PasswordAnswer", SqlDbType.NVarChar, 128).Value = EncodePassword(passwordAnswer, username);
                        cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = isApproved;
                        cmd.Parameters.Add("@IsLockedOut", SqlDbType.Bit).Value = false;
                        cmd.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = createDate;
                        cmd.Parameters.Add("@LastLoginDate", SqlDbType.DateTime).Value = createDate;
                        cmd.Parameters.Add("@LastPasswordChangedDate", SqlDbType.DateTime).Value = createDate;
                        cmd.Parameters.Add("@LastLockoutDate", SqlDbType.DateTime).Value = SqlDateTime.MinValue;
                        cmd.Parameters.Add("@FailedPasswordAttemptCount", SqlDbType.Int).Value = 0;
                        cmd.Parameters.Add("@FailedPasswordAttemptWindowStart", SqlDbType.DateTime).Value = SqlDateTime.MinValue;
                        cmd.Parameters.Add("@FailedPasswordAnswerAttemptCount", SqlDbType.Int).Value = 0;
                        cmd.Parameters.Add("@FailedPasswordAnswerAttemptWindowStart", SqlDbType.DateTime).Value = SqlDateTime.MinValue;
                        cmd.Parameters.Add("@Comment", SqlDbType.NText).Value = "";



                        SqlTransaction tran = null;

                        try
                        {
                            conn.Open();

                            tran = conn.BeginTransaction();
                            cmd.Transaction = tran;

                            var recAdded = cmd.ExecuteNonQuery();

                            if (recAdded > 0)
                            {
                                status = MembershipCreateStatus.Success;
                                tran.Commit();
                            }
                            else
                            {
                                status = MembershipCreateStatus.UserRejected;
                                tran.Rollback();
                            }
                        }

                        catch (SqlException e)
                        {
                            if (WriteExceptionsToEventLog)
                            {
                                WriteToEventLog(e, "CreateUser");
                            }
                            status = MembershipCreateStatus.ProviderError;
                        }
                        catch (Exception)
                        {
                            if (tran != null)
                            {
                                tran.Rollback();
                            }
                            status = MembershipCreateStatus.ProviderError;
                        }
                    }
                }
                return GetUser(username, false);
            }

            status = MembershipCreateStatus.DuplicateUserName;
            return null;
        }

        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            var rowsAffected = 0;

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("DELETE FROM [ServicesMembership] " +
                                                    " WHERE UserName = @UserName", conn))
                {
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                    SqlTransaction tran = null;

                    try
                    {
                        conn.Open();

                        tran = conn.BeginTransaction();

                        cmd.Transaction = tran;

                        rowsAffected = cmd.ExecuteNonQuery();

                        if (deleteAllRelatedData)
                        {
                            //TODO - Process commands to delete all data for the user in the database.
                        }

                        tran.Commit();
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "DeleteUser");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                    catch (Exception)
                    {
                        if (tran != null)
                        {
                            tran.Rollback();
                        }
                    }
                }
            }
            return rowsAffected > 0;
        }

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            var users = new MembershipUserCollection();

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT Count(*) FROM [ServicesMembership] ", conn))
                {
                    totalRecords = 0;

                    try
                    {
                        conn.Open();
                        totalRecords = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);

                        if (totalRecords <= 0)
                        {
                            return users;
                        }

                        cmd.CommandText = "SELECT UserName, Email, PasswordQuestion," +
                                          " Comment, IsApproved, IsLockedOut, CreateDate, LastLoginDate," +
                                          " LastActivityDate, LastPasswordChangedDate, LastLockoutDate " +
                                          " FROM [ServicesMembership]  " +
                                          " ORDER BY UserName Asc OFFSET @PageStart ROWS FETCH NEXT @PageSize ROWS ONLY ";

                        cmd.Parameters.Add("@PageStart", SqlDbType.Int).Value = pageIndex * pageSize;
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var u = GetUserFromReader(reader);
                                users.Add(u);
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "GetAllUsers");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
            return users;
        }

        public override int GetNumberOfUsersOnline()
        {
            var numOnline = 0;
            var onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
            var compareTime = DateTime.UtcNow.Subtract(onlineSpan);

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT Count(*) FROM [aspnet_Users] " +
                                                  " WHERE LastActivityDate > @CompareDate ", conn))
                {
                    cmd.Parameters.Add("@CompareDate", SqlDbType.DateTime).Value = compareTime;

                    try
                    {
                        conn.Open();

                        numOnline = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "GetNumberOfUsersOnline");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
            return numOnline;
        }

        public override string GetPassword(string username, string answer)
        {
            if (!EnablePasswordRetrieval)
            {
                throw new ProviderException("Password Retrieval Not Enabled.");
            }

            if (PasswordFormat == MembershipPasswordFormat.Hashed)
            {
                throw new ProviderException("Cannot retrieve Hashed passwords.");
            }

            var password = "";
            var passwordAnswer = "";

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT Password, PasswordAnswer, IsLockedOut " +
                                                "FROM [ServicesMembership] " +
                                                  " WHERE UserName = @UserName  ", conn))
                {
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                    try
                    {
                        conn.Open();
                        using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                        {
                            if (reader.Read())
                            {
                                if (reader.GetBoolean(2))
                                {
                                    throw new MembershipPasswordException("The supplied user is locked out.");
                                }
                                password = reader.GetString(0);
                                passwordAnswer = reader.GetString(1);
                            }
                            else
                            {
                                throw new MembershipPasswordException("The supplied user name is not found.");
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "GetPassword");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }

            if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer, username))
            {
                UpdateFailureCount(username, "passwordAnswer");

                throw new MembershipPasswordException("Incorrect password answer.");
            }


            if (PasswordFormat == MembershipPasswordFormat.Encrypted)
            {
                password = UnEncodePassword(password);
            }

            return password;
        }


        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            MembershipUser u = null;

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT UserName, Email, PasswordQuestion," +
                                                  " Comment, IsApproved, IsLockedOut, CreateDate, LastLoginDate," +
                                                  " LastActivityDate, LastPasswordChangedDate, LastLockoutDate" +
                                                  " FROM [ServicesMembership]" +
                                                  " WHERE UserName = @UserName  ", conn))
                {
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                    try
                    {
                        conn.Open();

                        using (var reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                u = GetUserFromReader(reader);

                                if (userIsOnline)
                                {
                                    using (var updateCmd = new SqlCommand("UPDATE [ServicesMembership] " +
                                                                            "SET LastActivityDate = @LastActivityDate " +
                                                                            "WHERE UserName = @UserName ", conn))
                                    {
                                        updateCmd.Parameters.Add("@LastActivityDate", SqlDbType.DateTime).Value = DateTime.UtcNow;
                                        updateCmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                                        updateCmd.ExecuteNonQuery();
                                    }
                                }
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "GetUser(String, Boolean)");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }

            return u;
        }

        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            MembershipUser u = null;

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT UserName, Email, PasswordQuestion," +
                                                  " Comment, IsApproved, IsLockedOut, CreateDate, LastLoginDate," +
                                                  " LastActivityDate, LastPasswordChangedDate, LastLockoutDate" +
                                                  " FROM [ServicesMembership] WHERE UserName = @UserName", conn))
                {
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = providerUserKey;

                    try
                    {
                        conn.Open();

                        using (var reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                u = GetUserFromReader(reader);

                                if (userIsOnline)
                                {
                                    using (var updateCmd = new SqlCommand("UPDATE [ServicesMembership] " +
                                                                            "SET LastActivityDate = @LastActivityDate " +
                                                                            "WHERE UserName = @UserName", conn))
                                    {
                                        updateCmd.Parameters.Add("@LastActivityDate", SqlDbType.DateTime).Value = DateTime.UtcNow;
                                        updateCmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = providerUserKey;

                                        updateCmd.ExecuteNonQuery();
                                    }
                                }
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "GetUser(Object, Boolean)");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
            return u;
        }

        public override bool UnlockUser(string userName)
        {
            var rowsAffected = 0;

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("UPDATE [ServicesMembership] " +
                                                  " SET IsLockedOut = 0, LastLockoutDate = @LastLockedOutDate " +
                                                  " WHERE UserName = @UserName  ", conn))
                {
                    cmd.Parameters.Add("@LastLockedOutDate", SqlDbType.DateTime).Value = DateTime.UtcNow;
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = userName;

                    try
                    {
                        conn.Open();

                        rowsAffected = cmd.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "UnlockUser");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
            return rowsAffected > 0;
        }

        //
        // MembershipProvider.GetUserNameByEmail
        //

        public override string GetUserNameByEmail(string email)
        {
            var username = "";

            if (string.IsNullOrWhiteSpace(email))
            {
                return string.Empty;
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT UserName " +
                                                  " FROM [ServicesMembership] " +
                                                    " WHERE Email = @Email  ", conn))
                {
                    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = email;

                    try
                    {
                        conn.Open();
                        var o = cmd.ExecuteScalar();
                        if (o != null)
                        {
                            username = Convert.ToString(o, CultureInfo.InvariantCulture);
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "GetUserNameByEmail");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }

            return username;
        }

        public override string ResetPassword(string username, string answer)
        {
            if (!EnablePasswordReset)
            {
                throw new NotSupportedException("Password reset is not enabled.");
            }

            if (answer == null && RequiresQuestionAndAnswer)
            {
                UpdateFailureCount(username, "passwordAnswer");

                throw new ProviderException("Password answer required for password reset.");
            }

            var newPassword =
                System.Web.Security.Membership.GeneratePassword(NewPasswordLength, MinRequiredNonAlphanumericCharacters);

            var args =
                new ValidatePasswordEventArgs(username, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                {
                    throw args.FailureInformation;
                }
                else
                {
                    throw new MembershipPasswordException("Reset password canceled due to password validation failure.");
                }
            }

            var rowsAffected = 0;
            var passwordAnswer = "";

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT PasswordAnswer, IsLockedOut FROM [ServicesMembership] " +
                                                " WHERE UserName = @UserName ", conn))
                {
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                    try
                    {
                        conn.Open();

                        using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                        {
                            if (reader.Read())
                            {
                                //object val = reader.GetValue(1);

                                if (Convert.ToBoolean(reader.GetValue(1)))
                                {
                                    throw new MembershipPasswordException("The supplied user is locked out.");
                                }

                                passwordAnswer = reader.GetString(0);
                            }
                            else
                            {
                                throw new MembershipPasswordException("The supplied user name is not found.");
                            }
                            if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer, username))
                            {
                                UpdateFailureCount(username, "passwordAnswer");

                                throw new MembershipPasswordException("Incorrect password answer.");
                            }
                        }

                        using (var updateCmd = new SqlCommand("UPDATE [ServicesMembership] " +
                                                              " SET Password = @Password, LastPasswordChangedDate = @LastPasswordChangedDate" +
                                                              " WHERE UserName = @UserName AND IsLockedOut = 0", conn))
                        {
                            updateCmd.Parameters.Add("@Password", SqlDbType.NVarChar, 128).Value = EncodePassword(newPassword, username);
                            updateCmd.Parameters.Add("@LastPasswordChangedDate", SqlDbType.DateTime).Value = DateTime.UtcNow;
                            updateCmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                            rowsAffected = updateCmd.ExecuteNonQuery();
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "ResetPassword");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
                if (rowsAffected > 0)
                {
                    return newPassword;
                }
                throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
            }
        }

        public override void UpdateUser(MembershipUser user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            if (RequiresUniqueEmail)
            {
                var userName = GetUserNameByEmail(user.Email);
                if (!string.IsNullOrWhiteSpace(userName)
                    && !userName.Equals(user.UserName, StringComparison.InvariantCultureIgnoreCase))
                {
                    throw new ProviderException("The e-mail address that you entered is already in use. Please enter a different e-mail address.");
                }
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("UPDATE [ServicesMembership] " +
                                                  " SET Email = @Email, Comment = @Comment," +
                                                  " IsApproved = @IsApproved, LastLoginDate = @LastLoginDate " +
                                                  " WHERE UserName = @UserName ", conn))
                {
                    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = user.Email;
                    cmd.Parameters.Add("@Comment", SqlDbType.NText).Value = user.Comment;
                    cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = user.IsApproved;
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = user.UserName;
                    cmd.Parameters.Add("@LastLoginDate", SqlDbType.DateTime).Value = user.LastLoginDate;

                    try
                    {
                        conn.Open();

                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "UpdateUser");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
        }

        public override bool ValidateUser(string username, string password)
        {
            var isValid = false;

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT Password, IsApproved FROM [ServicesMembership] " +
                                                  " WHERE UserName = @UserName AND IsLockedOut = 0", conn))
                {
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                    var isApproved = false;
                    var pwd = "";

                    try
                    {
                        conn.Open();

                        using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                        {
                            if (reader.Read())
                            {
                                pwd = reader.GetString(0);
                                var iApp = Convert.ToInt32(reader.GetValue(1));
                                if (iApp == 1)
                                {
                                    isApproved = true;
                                }
                            }
                            else
                            {
                                return false;
                            }
                        }

                        if (CheckPassword(password, pwd, username))
                        {
                            if (isApproved)
                            {
                                isValid = true;

                                using (var updateCmd = new SqlCommand("UPDATE [ServicesMembership] SET LastLoginDate = @LastLoginDate" +
                                                                        " WHERE UserName = @UserName ", conn))
                                {
                                    updateCmd.Parameters.Add("@LastLoginDate", SqlDbType.DateTime).Value = DateTime.UtcNow;
                                    updateCmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                                    updateCmd.ExecuteNonQuery();
                                }
                            }
                        }
                        else
                        {
                            UpdateFailureCount(username, "password");
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "ValidateUser");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
            return isValid;
        }

        /// <summary>
        /// UpdateFailureCount
        ///   A helper method that performs the checks and updates associated with
        /// password failure tracking.
        /// </summary>
        /// <param name="username"></param>
        /// <param name="failureType"></param>
        void UpdateFailureCount(string username, string failureType)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT FailedPasswordAttemptCount, " +
                                                  "  FailedPasswordAttemptWindowStart, " +
                                                  "  FailedPasswordAnswerAttemptCount, " +
                                                  "  FailedPasswordAnswerAttemptWindowStart " +
                                                  "  FROM [ServicesMembership] " +
                                                  "  WHERE UserName = @UserName  ", conn))
                {
                    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                    var windowStart = new DateTime();
                    var failureCount = 0;

                    try
                    {
                        conn.Open();

                        using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                        {
                            if (reader.Read())
                            {
                                if (failureType == "password")
                                {
                                    failureCount = reader.GetInt32(0);
                                    try
                                    {
                                        windowStart = reader.GetDateTime(1);
                                    }
                                    catch
                                    {
                                        windowStart = DateTime.UtcNow;
                                    }
                                }

                                if (failureType == "passwordAnswer")
                                {
                                    failureCount = reader.GetInt32(2);
                                    windowStart = reader.GetDateTime(3);
                                }
                            }
                        }

                        var windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);

                        if (failureCount == 0
                            || DateTime.UtcNow > windowEnd)
                        {
                            // First password failure or outside of PasswordAttemptWindow.
                            // Start a new password failure count from 1 and a new window starting now.

                            if (failureType == "password")
                            {
                                cmd.CommandText = "UPDATE [ServicesMembership] " +
                                                  "  SET FailedPasswordAttemptCount = @Count, " +
                                                  "      FailedPasswordAttemptWindowStart = @WindowStart " +
                                                  "  WHERE UserName = @UserName ";
                            }

                            if (failureType == "passwordAnswer")
                            {
                                cmd.CommandText = "UPDATE [ServicesMembership] " +
                                                  "  SET FailedPasswordAnswerAttemptCount = @Count, " +
                                                  "      FailedPasswordAnswerAttemptWindowStart = @WindowStart " +
                                                  "  WHERE UserName = @UserName ";
                            }

                            cmd.Parameters.Clear();

                            cmd.Parameters.Add("@Count", SqlDbType.Int).Value = 1;
                            cmd.Parameters.Add("@WindowStart", SqlDbType.DateTime).Value = DateTime.UtcNow;
                            cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                            if (cmd.ExecuteNonQuery() < 0)
                            {
                                throw new ProviderException("Unable to update failure count and window start.");
                            }
                        }
                        else
                        {
                            if (failureCount++ >= MaxInvalidPasswordAttempts)
                            {
                                // Password attempts have exceeded the failure threshold. Lock out
                                // the user.

                                cmd.CommandText = "UPDATE [ServicesMembership] " +
                                                  "  SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockedOutDate " +
                                                  "  WHERE UserName = @UserName ";

                                cmd.Parameters.Clear();

                                cmd.Parameters.Add("@IsLockedOut", SqlDbType.Bit).Value = true;
                                cmd.Parameters.Add("@LastLockedOutDate", SqlDbType.DateTime).Value = DateTime.UtcNow;
                                cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                                if (cmd.ExecuteNonQuery() < 0)
                                {
                                    throw new ProviderException("Unable to lock out user.");
                                }
                            }
                            else
                            {
                                // Password attempts have not exceeded the failure threshold. Update
                                // the failure counts. Leave the window the same.

                                if (failureType == "password")
                                {
                                    cmd.CommandText = "UPDATE [ServicesMembership] " +
                                                      "  SET FailedPasswordAttemptCount = @Count" +
                                                      "  WHERE UserName = @UserName ";
                                }

                                if (failureType == "passwordAnswer")
                                {
                                    cmd.CommandText = "UPDATE [ServicesMembership] " +
                                                      "  SET FailedPasswordAnswerAttemptCount = @Count" +
                                                      "  WHERE UserName = @UserName ";
                                }

                                cmd.Parameters.Clear();

                                cmd.Parameters.Add("@Count", SqlDbType.Int).Value = failureCount;
                                cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;

                                if (cmd.ExecuteNonQuery() < 0)
                                {
                                    throw new ProviderException("Unable to update failure count.");
                                }
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "UpdateFailureCount");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
        }



        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            var users = new MembershipUserCollection();

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT Count(*) FROM [aspnet_Users] " +
                                                  "WHERE [aspnet_Users].UserName LIKE @UserNameSearch ", conn))
                {
                    cmd.Parameters.Add("@UserNameSearch", SqlDbType.NVarChar, 256).Value = usernameToMatch;

                    try
                    {
                        conn.Open();
                        totalRecords = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);

                        if (totalRecords <= 0)
                        {
                            return users;
                        }

                        cmd.CommandText = "SELECT UserName, Email, PasswordQuestion," +
                                          " Comment, IsApproved, IsLockedOut, CreateDate, LastLoginDate," +
                                          " LastActivityDate, LastPasswordChangedDate, LastLockoutDate " +
                                          " FROM [ServicesMembership] " +
                                          " WHERE UserName LIKE @UserNameSearch " +
                                          " ORDER BY UserName Asc OFFSET @PageStart ROWS FETCH NEXT @PageSize ROWS ONLY ";

                        cmd.Parameters.Add("@PageStart", SqlDbType.Int).Value = pageIndex * pageSize;
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var u = GetUserFromReader(reader);
                                users.Add(u);
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "FindUsersByName");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
            return users;
        }


        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            var users = new MembershipUserCollection();

            using (var conn = new SqlConnection(_connectionString))
            {
                using (var cmd = new SqlCommand("SELECT Count(*) FROM [ServicesMembership] " +
                                                  "WHERE Email LIKE @EmailSearch ", conn))
                {
                    cmd.Parameters.Add("@EmailSearch", SqlDbType.NVarChar, 256).Value = emailToMatch;

                    totalRecords = 0;

                    try
                    {
                        conn.Open();
                        totalRecords = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);

                        if (totalRecords <= 0)
                        {
                            return users;
                        }

                        cmd.CommandText = "SELECT UserName, Email, PasswordQuestion," +
                                          " Comment, IsApproved, IsLockedOut, CreateDate, LastLoginDate," +
                                          " LastActivityDate, LastPasswordChangedDate, LastLockoutDate " +
                                          " FROM [ServicesMembership], [aspnet_Users] " +
                                          " WHERE Email LIKE @EmailSearch " +
                                          " ORDER BY UserName Asc OFFSET @PageStart ROWS FETCH NEXT @PageSize ROWS ONLY ";

                        cmd.Parameters.Add("@PageStart", SqlDbType.Int).Value = pageIndex * pageSize;
                        cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var u = GetUserFromReader(reader);
                                users.Add(u);
                            }
                        }
                    }
                    catch (SqlException e)
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "FindUsersByEmail");

                            throw new ProviderException(ExceptionMessage);
                        }
                        throw;
                    }
                }
            }
            return users;
        }

        #endregion  System.Web.Security.MembershipProvider

        #region protected

        MembershipUser GetUserFromReader(SqlDataReader reader)
        {
            if (string.IsNullOrWhiteSpace(reader.GetString(0)))
            {
                return null;
            }

            var username = reader.GetString(0);

            var email = reader.GetString(1);

            var passwordQuestion = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);

            var comment = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);

            var isApproved = reader.IsDBNull(4) ? false : reader.GetBoolean(4);

            var isLockedOut = reader.IsDBNull(5) ? false : reader.GetBoolean(5);

            var creationDate = reader.IsDBNull(6) ? DateTime.UtcNow : reader.GetDateTime(6);

            var lastLoginDate = reader.IsDBNull(7) ? DateTime.UtcNow : reader.GetDateTime(7);

            var lastActivityDate = reader.IsDBNull(8) ? DateTime.UtcNow : reader.GetDateTime(8);

            var lastPasswordChangedDate = reader.IsDBNull(9) ? DateTime.UtcNow : reader.GetDateTime(9);

            var lastLockedOutDate = reader.IsDBNull(10) ? DateTime.UtcNow : reader.GetDateTime(10);

            var user = new MembershipUser(Name,
                username,
                username,
                email,
                passwordQuestion,
                comment,
                isApproved,
                isLockedOut,
                creationDate,
                lastLoginDate,
                lastActivityDate,
                lastPasswordChangedDate,
                lastLockedOutDate);

            return user;
        }

        bool CheckPassword(string password, string dbpassword, string username)
        {
            var pass1 = password;
            var pass2 = dbpassword;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Encrypted:
                    pass2 = UnEncodePassword(dbpassword);
                    pass1 = password + "|" + username;
                    break;
                case MembershipPasswordFormat.Hashed:
                    pass1 = EncodePassword(password, username);
                    break;
                default:
                    break;
            }

            return pass1 == pass2;
        }

        /// <summary>
        /// EncodePassword
        ///   Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
        /// </summary>
        /// <param name="password"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        string EncodePassword(string password, string userName)
        {
            Contract.Requires(userName.IsNotEmpty());
            if (password.IsEmpty())
            {
                return string.Empty;
            }

            var pwd = userName.ToLowerInvariant() + "|" + password;

            var encodedPassword = pwd;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    encodedPassword =
                        Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(pwd)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    using (var hash = new HMACSHA1())
                    {
                        hash.Key = HexToByte(_machineKey.ValidationKey);
                        encodedPassword =
                            Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(pwd)));
                    }
                    break;
                default:
                    throw new ProviderException("Unsupported password format.");
            }

            return encodedPassword;
        }

        /// <summary>
        ///  UnEncodePassword
        //   Decrypts or leaves the password clear based on the PasswordFormat.
        /// </summary>
        /// <param name="encodedPassword"></param>
        /// <returns></returns>
        string UnEncodePassword(string encodedPassword)
        {
            var password = encodedPassword;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    password =
                        Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    throw new ProviderException("Cannot decode a hashed password.");
                default:
                    throw new ProviderException("Unsupported password format.");
            }

            return password;
        }

        /// <summary>
        ///  HexToByte
        ///   Converts a hexadecimal string to a byte array. Used to convert encryption
        /// key values from the configuration.
        /// </summary>
        /// <param name="hexString"></param>
        /// <returns></returns>
        byte[] HexToByte(string hexString)
        {
            var returnBytes = new byte[hexString.Length / 2];
            for (var i = 0; i < returnBytes.Length; i++)
            {
                returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
            }
            return returnBytes;
        }

        #endregion protected


        //
        // WriteToEventLog
        //   A helper function that writes exception detail to the event log. Exceptions
        // are written to the event log as a security measure to avoid private database
        // details from being returned to the browser. If a method does not return a status
        // or boolean indicating the action succeeded or failed, a generic exception is also
        // thrown by the caller.
        //

        void WriteToEventLog(Exception e, string action)
        {
            using (var log = new EventLog())
            {
                log.Source = EventSource;
                log.Log = EventLog;

                var message = "An exception occurred communicating with the data source.\n\n";
                message += "Action: " + action + "\n\n";
                message += "Exception: " + e;

                log.WriteEntry(message);
            }
        }
    }
}